'  ==========================================================================
'  |                                                                        |
'  | SQLitening Example B                                                   |
'  |                                                                        |
'  ==========================================================================
'  This meaningless program will create two new databases.  Insert a
'  row into the first (attached) one and then get it back comparing
'  to be sure its the same.  This demo's encryption and compression
'  using the slExeBind and slBuildInsertOrUpdate commands.
'  It will then insert 50,000 records twice.  Once using slExeBind and
'  then a second time using slExe.  slExeBind will be faster.
'
'  ==========================================================================
'  PUBLIC DOMAIN SOFTWARE
'  The author or authors of this code dedicate any and all
'  copyright interest in this code to the public domain.
'  Anyone is free to copy, modify, publish, use, compile,
'  sell, or distribute the original code, either in source
'  code form or as a compiled binary, for any purpose,
'  commercial or non-commercial, and by any means.
'  Fred Meier - July 2010
'  ==========================================================================

#Compile Exe "..\Bin\ExampleB.Exe"
#Dim All
#Include "..\Inc\SQLitening.Inc"

'============================<[ Globals ]>=============================
Global gsTitle as String

'==============================<[ Main ]>==============================
Function PbMain()

   Local llDo as Long
   Local lsServer as String
   Local lsPort as String
   Local ldTime1, ldTime2 as Double
   Dim lsaRows() as String

' ==========>>>    Ask if they want to run local or remote
   select case msgbox("Do you want to run Local or Remote mode?" & $CR & $CR & "Yes = Local             No = Remote", %MB_YESNOCANCEL, "SQLitening Example C")
   case %IDNO
      lsServer = "LocalHost"
      lsServer = inputbox$("Enter remote server name", gsTitle, lsServer)
      if isfalse len(lsServer) then exit function
      lsPort = "51234"
      lsPort = inputbox$("Enter remote port number", gsTitle, lsPort)
      if isfalse len(lsPort) then exit function
      gsTitle = "SQLitening Example B -- Remote Mode (" & lsServer & "  " & lsPort & ")"
   case %IDYES
      lsServer = $NUL
      gsTitle = "SQLitening Example B -- Local Mode"
   case %IDCANCEL
      exit function
   end select

   ' ==========>>> SQLitening -- Connect
   ' Will connect to a SQLitening server.  This is only required if
   ' you want to run in remote mode. Do not call this routine or set
   ' Server to $NUL if you want to run in local mode. If Server is
   ' omitted or empty it will default to LocalHost.
   slConnect lsServer, val(lsPort)

   ' Create new database
   slOpen "ExampleB_New1.Sld", "C"
   slExe "Drop Table If Exists T1"
   slExe "Create Table If Not Exists T1 (F1, F2, F3, F4)"
   slClose

   ' Create second new database and attact the first
   slOpen "ExampleB_New2.Sld", "C"
   slExe "Drop Table If Exists T1"
   slExe "Create Table If Not Exists T1 (F1, F2, F3)"
   slAttach "ExampleB_New1.Sld", "One"

   ' Insert some rows with encrypted and compressed fields into the first database
   slSetProcessMods "K1234567890123456"
   slExeBind slBuildInsertOrUpdate("One.T1", "?" & $NUL & "?" & $NUL & "?" & $NUL & "Just some data"), _
                                    slBuildBindDat("Encrypt and Compress as Text", "TNC") & _
                                    slBuildBindDat("Encrypt and Compress as Blob", "BNC") & _
                                    slBuildBindDat("Neither as Text", "T")

   ' Now we will get the fields and make sure they equal the original
   slSel "Select * from One.T1"
   do while slGetRow
      if slFX(1, "DU")  <> "Encrypt and Compress as Text" then msgbox "Field 1 is different"
      if slFX(2, "DU") <> "Encrypt and Compress as Blob" then msgbox "Field 2 is different"
      if slF(3) <> "Neither as Text" then msgbox "Field 3 is different"
   loop

   ' Now we will use slExeBind to do a large number of inserts.
   ' Using slExeBind this way tells SQLite3 to only prepare/compile
   ' the SQL statement one time and then insert each row re-using the
   ' prepared/compiled statement.  We also load all of the BindDat's
   ' into an array and then use the join$ command to build a single
   ' string --- much faster than concatenating each string. These two
   ' techniques are a way to insert or update many records very fast.

   ldTime1 = timer
   redim lsaRows(1 to 100000)
   for llDo = 1 to ubound(lsaRows)
      lsaRows(llDo) = slBuildBindDat(format$(llDo))
   next
   slExe "Begin"
   slExeBind "Insert into T1 values(?, 'X', ?)", join$(lsaRows(), ""), "V2"
   slExe "End"
   ldTime1 = timer - ldTime1
   msgbox "We inserted " & format$(ubound(lsaRows)/2, "#,###") & " records in just" & format$(ldTime1, "* #.000") & " seconds using slExeBind.", , gsTitle

   ' Now we will use slExe to do a large number of inserts.  This
   ' will be slower than using slExeBind.
   ldTime2 = timer
   redim lsaRows(1 to 50000)
   for llDo = 1 to ubound(lsaRows)
      lsARows(llDo) = "Insert into T1 values(1" & format$(llDo) & ", 'X', 2" & format$(llDo) & ")"
   next
   slExe "Begin;" & join$(lsaRows(), ";") & ";End"
   ldTime2 = timer - ldTime2
   msgbox "We inserted " & format$(ubound(lsaRows), "#,###") & " records in " & format$(ldTime2, "* #.000") & " seconds using slExe.", , gsTitle
   msgbox "Notice that slExeBind was " & format$((ldTime2 - ldTime1) / ldTime2 * 100, "00.00") & "% faster then slExe.", , gsTitle

   ' ==========>>>    Done
   slClose
   if lsServer = $NUL then
      if msgbox("End of SQLitening Example B.  Do you want to delete the two databases used in this example?", %MB_YESNO, gsTitle) = %IDYES then
         kill "ExampleB_New1.Sld"
         kill "ExampleB_New2.Sld"
      end if
   else
      msgbox "End of SQLitening Example B.   The following two files were created in the same folder as the service which is running on your server.  You will want to delete them manually." & $CR & $CR & _
             "ExampleB_New1.Sld" & $cr & "ExampleB_New2.Sld", ,gsTitle
   end if

End Function
